package br.com.virtualsoluction.dao;

import br.com.virtualsoluction.bo.Pessoa;
import br.com.virtualsoluction.utilitarios.ConnectionFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class DAOPessoa implements CRUD<Pessoa>{

    private String sql;
    private Connection c;
    private PreparedStatement s;
    private ResultSet rs;
    
    public DAOPessoa(){
        this.sql = "";
        this.c = new ConnectionFactory().getConnection();
        this.s = null;
        this.rs = null;        
    } 
    
    public boolean insert(Pessoa elemento) {
        int i;
        boolean b = false;
        i = getChave();
        this.sql =  "insert into cliente (id, cpfcnpj, razaosocial)";
        this.sql += " values ( "+i+" , '"+elemento.getCpfCNPJ() +"' , '"+elemento.getRazaoSocial() +"' )";

        

        try {
            this.s = this.c.prepareStatement(this.sql);
            //this.s.setInt(1, i);
            //this.s.setString(2, elemento.getCpfCNPJ());
            //this.s.setString(3, elemento.getRazaoSocial());
            b = this.s.execute();
            return b;
        } catch (SQLException ex) {
            return false;
        }
    }

    public boolean update(Pessoa elemento) {
        this.sql =  "update cliente ";
        this.sql += "set cpfcnpj = ? , ";
        this.sql += "razaosocial = ? ";
        this.sql += "where (id = ? )";

        try{
            this.s = this.c.prepareStatement(this.sql);
            this.s.setString(1, elemento.getCpfCNPJ());
            this.s.setString(2, elemento.getRazaoSocial());
            this.s.setInt(3, elemento.getId());
            this.s.executeUpdate();

            return true;

        }catch(SQLException ex){
            return false;
        }
    }

    public boolean delete(Pessoa elemento) {
        this.sql =  "delete from pessoa";
        this.sql += "where (id = ? )";

        try{
            this.s = this.c.prepareStatement(this.sql);
            this.s.setInt(1, elemento.getId());
            this.s.execute();
            return true;
        }catch(SQLException ex){
            return false;
        }
    }

    public Pessoa search(int id) {
         this.sql = "select id, cpfcnpj, razaosocial from cliente where id = ? ";

        try {
            this.s = c.prepareStatement(this.sql);
            this.s.setInt(1, id);
            this.rs = this.s.executeQuery();
            rs.next();
            Pessoa pt = new Pessoa();
            pt.setId(rs.getInt("id"));
            pt.setRazaoSocial(rs.getString("razaosocial"));
            pt.setCpfCNPJ(rs.getString("cpfcnpj"));
            return pt;

        } catch (SQLException ex) {
            Logger.getLogger(DAOPessoa.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }

    public List<Pessoa> getList() {
        List<Pessoa> pess = new ArrayList<Pessoa>();
        this.sql =  "select id, cpfcnpj, razaosocial from cliente order by id";

        try {
            this.s = c.prepareStatement(this.sql);
            this.rs = this.s.executeQuery();

            while(rs.next()){
                Pessoa pt = new Pessoa();
                pt.setId(rs.getInt("id"));
                pt.setRazaoSocial(rs.getString("razaosocial"));
                pt.setCpfCNPJ(rs.getString("cpfcnpj"));
                pess.add(pt);
            }
            return pess;

        } catch (SQLException ex) {
            Logger.getLogger(DAOPessoa.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    
    public int getChave(){
        
        int ret = 0;
        this.sql = "select gen_id(gen_pessoa_id,1) from rdb$database";
        try {
            this.s = this.c.prepareStatement(this.sql);
        } catch (SQLException ex) {
            Logger.getLogger(DAOPessoa.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            this.rs = this.s.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(DAOPessoa.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rs.next();
            ret = rs.getInt("gen_id");
        } catch (SQLException ex) {
            Logger.getLogger(DAOPessoa.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
    }        

}